package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.ICompanyDao;
import com.cn.wanxi.dto.CompanyModelDto;
import com.cn.wanxi.model.CompanyModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 1.编写sql语句
 * 2.调用JDBC
 * 3.返回结果集
 */
public class CompanyImpl implements ICompanyDao {
    /**
     * alt+enter
     * 得到公司信息的数据层
     *
     * @return
     */
    @Override
    public CompanyModel getCommonModelDto() {

        String sql = "select * from company_one order by id desc limit 1";
        ResultSet resultSet = JDBC.getResultSet(sql);
        CompanyModel companyModel = null;
        try {
            while (resultSet.next()) {
                companyModel = new CompanyModel();
                //resultSet.getString("net")  这里的net对应的是数据库字段名称

                companyModel.setId(resultSet.getInt("id"));
                companyModel.setNet(resultSet.getString("net"));
                companyModel.setEmail(resultSet.getString("email"));
                companyModel.setVersion(resultSet.getString("version"));
                companyModel.setAd(resultSet.getString("ad"));
                companyModel.setAddress(resultSet.getString("address"));
                companyModel.setLinePhone(resultSet.getString("linePhone"));
                companyModel.setLogo(resultSet.getString("logo"));
                companyModel.setPhone(resultSet.getString("phone"));
                companyModel.setName(resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }

        return companyModel;
    }

    /**
     * 新增公司信息
     *
     * @param companyModel
     * @return
     */
    @Override
    public int add(CompanyModel companyModel) {
        String sql = "INSERT INTO `company_one` " +
                "(`phone`, `logo`, `ad`, `net`, `email`, `linePhone`, `address`, `version`, `content`)" +
                "VALUES" +
                "('" + companyModel.getPhone() + "'," +
                " '" + companyModel.getLogo() + "', " +
                "'" + companyModel.getAd() + "', " +
                "'" + companyModel.getNet() + "', " +
                "'" + companyModel.getEmail() + "'," +
                " '" + companyModel.getLinePhone() + "'," +
                " '" + companyModel.getAddress() + "'," +
                " '" + companyModel.getVersion() + "', " +
                "'" + companyModel.getContent() + "');";
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public int update(CompanyModel companyModel) {
        String sql = "UPDATE `company_one` SET " +
                "`phone` = '" + companyModel.getPhone() + "', " +
                "`logo` = '" + companyModel.getLogo() + "', " +
                "`ad` = '" + companyModel.getAd() + "', " +
                "`net` = '" + companyModel.getNet() + "', " +
                "`email` = '" + companyModel.getEmail() + "', " +
                "`linePhone` = '" + companyModel.getLinePhone() + "', " +
                "`address` = '" + companyModel.getAddress() + "', " +
                "`version` = '" + companyModel.getVersion() + "', " +
                "`content` = '" + companyModel.getContent() + "'";

        return JDBC.excuteUpdate(sql);
    }

    @Override
    public int del(CompanyModel companyModel) {
        String sql = "delete from company_one where id=" + companyModel.getId();
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public List<CompanyModel> findAll() {
        String sql = "select * from company_one";
        ResultSet resultSet = JDBC.getResultSet(sql);
        List<CompanyModel> lsit = new ArrayList<>();
        CompanyModel companyModel = null;
        try {
            while (resultSet.next()) {
                companyModel = new CompanyModel();
                companyModel.setId(resultSet.getInt("id"));
                companyModel.setNet(resultSet.getString("net"));
                companyModel.setEmail(resultSet.getString("email"));
                companyModel.setVersion(resultSet.getString("version"));
                companyModel.setAd(resultSet.getString("ad"));
                companyModel.setAddress(resultSet.getString("address"));
                companyModel.setLinePhone(resultSet.getString("linePhone"));
                companyModel.setLogo(resultSet.getString("logo"));
                companyModel.setPhone(resultSet.getString("phone"));
                companyModel.setContent(resultSet.getString("content"));
                companyModel.setName(resultSet.getString("name"));
                lsit.add(companyModel);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return lsit;
    }

    /**
     * 根据ID进行查询
     *
     * @param companyModel
     * @return
     */
    @Override
    public CompanyModel findById(CompanyModel companyModel) {
        String sql = "select * from company_one where id=" + companyModel.getId();
        ResultSet resultSet = JDBC.getResultSet(sql);
        CompanyModel model = null;
        try {
            while (resultSet.next()) {
                model = new CompanyModel();
                model.setId(resultSet.getInt("id"));
                model.setNet(resultSet.getString("net"));
                model.setEmail(resultSet.getString("email"));
                model.setVersion(resultSet.getString("version"));
                model.setAd(resultSet.getString("ad"));
                model.setAddress(resultSet.getString("address"));
                model.setLinePhone(resultSet.getString("linePhone"));
                model.setLogo(resultSet.getString("logo"));
                model.setPhone(resultSet.getString("phone"));
                model.setContent(resultSet.getString("content"));
                companyModel.setName(resultSet.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return model;
    }

    /**
     * 数据实现层做四件事情
     * 1.封装sql语句
     * 2.执行sql语句
     * 3.封装返回对象
     * 4.返回数据
     *
     * @param companyModel
     * @return
     */
    @Override
    public List<CompanyModel> findBySome(CompanyModel companyModel) {
        //现在需要对sql语句进行拼接
        String sql = "select * from company_one where 1=1";//封装SQL语句
        //拼接sql语句有几种情况，
        // 1.如果都没有值，则查询所有
        // 2.如果只有电话：则条件语句只能有电话，其他同理
        if (!companyModel.getPhone().equals("")) {
            sql += " and phone like '%" + companyModel.getPhone() + "%'";
        }
        if (!companyModel.getName().equals("")) {
            sql += " and name  like '%" + companyModel.getName() + "%'";
        }
        if (!companyModel.getNet().equals("")) {
            sql += " and net like '%" + companyModel.getNet() + "%'";
        }
        if (!companyModel.getContent().equals("")) {
            sql += " and content like '%" + companyModel.getContent() + "%'";
        }
        if (!companyModel.getAddress().equals("")) {
            sql += " and address like '%" + companyModel.getAddress() + "%'";
        }

        //sql还有一个叫做模糊查询 like '%aa%' 左右匹配 'aa%' 必须以aa结尾 '%aa'必须以aa开始
        //select * from company_one where 1=1 and phone=2552 and name=fads
        ResultSet resultSet = JDBC.getResultSet(sql);
        List<CompanyModel> lsit = new ArrayList<>();

        try {
            while (resultSet.next()) {
                CompanyModel mode = new CompanyModel();
                mode.setId(resultSet.getInt("id"));
                mode.setNet(resultSet.getString("net"));
                mode.setEmail(resultSet.getString("email"));
                mode.setVersion(resultSet.getString("version"));
                mode.setAd(resultSet.getString("ad"));
                mode.setAddress(resultSet.getString("address"));
                mode.setLinePhone(resultSet.getString("linePhone"));
                mode.setLogo(resultSet.getString("logo"));
                mode.setPhone(resultSet.getString("phone"));
                mode.setContent(resultSet.getString("content"));
                mode.setName(resultSet.getString("name"));
                lsit.add(mode);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return lsit;
    }
}
